﻿GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_UtilityAssetDistribution_SelectForMeterToAssetGrid')
	DROP PROCEDURE up_UtilityAssetDistribution_SelectForMeterToAssetGrid
GO

CREATE PROCEDURE [dbo].[up_UtilityAssetDistribution_SelectForMeterToAssetGrid]
(
	@projectFk int
	,@PageSize int = 100
	,@PageNumber int = 1
	,@feets int = 20
)
AS

--Local variables
DECLARE @StartRowNumber int
DECLARE @EndRowNumber int
DECLARE @UtilityId int

SET @StartRowNumber = ((@PageNumber - 1) * @PageSize) + 1
SET @EndRowNumber = @PageNumber * @PageSize

SELECT 
	@UtilityId = UtilityFk
FROM
	tb_vendor_projects
WHERE
	Vendor_ProjectId = @projectFk

SELECT 
	Data.Vendor_Distribution_Meters_MeterId InspVisitId
	,ISNULL(ISNULL(
			CAST(Data.Utility_Asset_Meters_MeterFk as varchar), 
			(
				--Gets closest
				SELECT TOP 1
					CAST(assets.Utility_Asset_MeterId as varchar) Utility_Asset_DistributionId
				FROM 
					tb_Utility_Asset_Meters assets
				WHERE
					assets.UtilityFk = @UtilityId
					AND dbo.fn_DistanceBetween(Data.Latitude, Data.Longitude, assets.Latitude, assets.Longitude) < @feets
			)
		), '<<new>>') AssetId
	,TotalCount
FROM 
	(
		SELECT 
			ROW_NUMBER() OVER (ORDER BY meters.Vendor_Distribution_Meters_MeterId) AS RowNumber
			, COUNT(meters.Vendor_Distribution_Meters_MeterId) OVER (PARTITION BY NULL) AS TotalCount
			, meters.Vendor_Distribution_Meters_MeterId
			, meters.Utility_Asset_Meters_MeterFk
			, meters.Latitude
			, meters.Longitude
		FROM 
			tb_Vendor_Distribution_Meters meters
		INNER JOIN tb_Vendor_Distribution_Poles poles
			ON meters.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleId
		WHERE
			poles.ProjectFk = @projectFk
	) DATA
	LEFT JOIN tb_Utility_Asset_Meters assets
		ON DATA.Utility_Asset_Meters_MeterFk = assets.Utility_Asset_MeterId
WHERE 
	RowNumber BETWEEN @StartRowNumber AND @EndRowNumber 


 